package repository

import (
	"fitness/go-admin/internal/model"
	"fmt"
	"time"

	"gorm.io/gorm"
)

// ==================== 统计仓储接口 ====================

type StatisticsRepository interface {
	// 会员统计
	GetTotalMembers() (int64, error)
	GetNewMembersCount(startDate, endDate time.Time) (int64, error)
	GetActiveMembersCount() (int64, error)
	GetExpiredMembersCount() (int64, error)
	GetMemberGrowthByDate(startDate, endDate time.Time, groupBy string) ([]model.MemberGrowthData, error)
	GetMemberDistribution(dimension string, startDate, endDate *time.Time) ([]model.DistributionItem, error)

	// 课程统计
	GetTotalCourses() (int64, error)
	GetTodayBookingsCount(date time.Time) (int64, error)
	GetCompletedBookingsCount(date time.Time) (int64, error)
	GetCourseRanking(startDate, endDate time.Time, orderBy string, limit int) ([]model.CourseRankItem, error)
	GetCourseBookingTrends(courseID *uint, startDate, endDate time.Time, groupBy string) (*model.CourseBookingTrendsResp, error)
	GetTimeslotStats(startDate, endDate time.Time) ([]model.TimeslotData, error)

	// 教练统计
	GetTotalCoaches() (int64, error)
	GetActiveCoachesCount() (int64, error)
	GetCoachWorkload(coachID *uint, startDate, endDate time.Time) ([]model.CoachWorkload, error)
	GetCoachRanking(startDate, endDate time.Time, orderBy string, limit int) ([]model.CoachRankItem, error)

	// 营收统计
	GetTotalRevenue() (float64, error)
	GetRevenueByDateRange(startDate, endDate time.Time) (float64, error)
	GetRevenueByPaymentMethod(startDate, endDate time.Time) ([]model.PaymentMethodStats, error)
	GetRevenueTrends(startDate, endDate time.Time, groupBy, revenueType string) ([]model.RevenueTrendData, error)
	GetConsumptionStats(startDate, endDate time.Time, consumeType *string) (*model.ConsumptionStatsResp, error)

	// 会员卡统计
	GetActiveCardsCount() (int64, error)
	GetTotalCardsBalance() (float64, error)
	GetExpiringSoonCardsCount(days int) (int64, error)
}

type statisticsRepository struct {
	db *gorm.DB
}

func NewStatisticsRepository(db *gorm.DB) StatisticsRepository {
	return &statisticsRepository{db: db}
}

// ==================== 会员统计实现 ====================

func (r *statisticsRepository) GetTotalMembers() (int64, error) {
	var count int64
	err := r.db.Model(&model.Member{}).Count(&count).Error
	return count, err
}

func (r *statisticsRepository) GetNewMembersCount(startDate, endDate time.Time) (int64, error) {
	var count int64
	err := r.db.Model(&model.Member{}).
		Where("created_at BETWEEN ? AND ?", startDate, endDate).
		Count(&count).Error
	return count, err
}

func (r *statisticsRepository) GetActiveMembersCount() (int64, error) {
	var count int64
	err := r.db.Model(&model.Member{}).
		Where("status = ?", 1).
		Count(&count).Error
	return count, err
}

func (r *statisticsRepository) GetExpiredMembersCount() (int64, error) {
	var count int64
	err := r.db.Model(&model.Member{}).
		Where("expire_date < ? AND status = ?", time.Now(), 1).
		Count(&count).Error
	return count, err
}

func (r *statisticsRepository) GetMemberGrowthByDate(startDate, endDate time.Time, groupBy string) ([]model.MemberGrowthData, error) {
	type Result struct {
		Date       string
		NewMembers int64
	}

	var results []Result
	var dateFormat string

	switch groupBy {
	case "day":
		dateFormat = "%Y-%m-%d"
	case "week":
		dateFormat = "%Y-%u"
	case "month":
		dateFormat = "%Y-%m"
	default:
		dateFormat = "%Y-%m-%d"
	}

	err := r.db.Model(&model.Member{}).
		Select("DATE_FORMAT(created_at, ?) as date, COUNT(*) as new_members", dateFormat).
		Where("created_at BETWEEN ? AND ?", startDate, endDate).
		Group("date").
		Order("date").
		Scan(&results).Error

	if err != nil {
		return nil, err
	}

	// 转换并计算累计值
	var growthData []model.MemberGrowthData
	var cumulative int64

	for _, r := range results {
		cumulative += r.NewMembers
		growthData = append(growthData, model.MemberGrowthData{
			Date:       r.Date,
			NewMembers: r.NewMembers,
			Cumulative: cumulative,
		})
	}

	return growthData, nil
}

func (r *statisticsRepository) GetMemberDistribution(dimension string, startDate, endDate *time.Time) ([]model.DistributionItem, error) {
	type Result struct {
		Label string
		Count int64
	}

	var results []Result
	query := r.db.Model(&model.Member{})

	if startDate != nil && endDate != nil {
		query = query.Where("created_at BETWEEN ? AND ?", *startDate, *endDate)
	}

	var selectField string
	switch dimension {
	case "type":
		selectField = "member_type"
	case "status":
		selectField = "status"
	case "gender":
		selectField = "gender"
	default:
		selectField = "member_type"
	}

	err := query.
		Select(selectField + " as label, COUNT(*) as count").
		Group(selectField).
		Order("count DESC").
		Scan(&results).Error

	if err != nil {
		return nil, err
	}

	// 计算总数和百分比
	var total int64
	for _, r := range results {
		total += r.Count
	}

	var distribution []model.DistributionItem
	for _, r := range results {
		percentage := 0.0
		if total > 0 {
			percentage = float64(r.Count) / float64(total) * 100
		}
		distribution = append(distribution, model.DistributionItem{
			Label:      r.Label,
			Count:      r.Count,
			Percentage: percentage,
		})
	}

	return distribution, nil
}

// ==================== 课程统计实现 ====================

func (r *statisticsRepository) GetTotalCourses() (int64, error) {
	var count int64
	err := r.db.Model(&model.Course{}).Count(&count).Error
	return count, err
}

func (r *statisticsRepository) GetTodayBookingsCount(date time.Time) (int64, error) {
	var count int64
	startOfDay := time.Date(date.Year(), date.Month(), date.Day(), 0, 0, 0, 0, date.Location())
	endOfDay := startOfDay.Add(24 * time.Hour)

	err := r.db.Model(&model.CourseBooking{}).
		Where("created_at BETWEEN ? AND ?", startOfDay, endOfDay).
		Count(&count).Error
	return count, err
}

func (r *statisticsRepository) GetCompletedBookingsCount(date time.Time) (int64, error) {
	var count int64
	startOfDay := time.Date(date.Year(), date.Month(), date.Day(), 0, 0, 0, 0, date.Location())
	endOfDay := startOfDay.Add(24 * time.Hour)

	err := r.db.Model(&model.CourseBooking{}).
		Where("created_at BETWEEN ? AND ? AND status = ?", startOfDay, endOfDay, "COMPLETED").
		Count(&count).Error
	return count, err
}

func (r *statisticsRepository) GetCourseRanking(startDate, endDate time.Time, orderBy string, limit int) ([]model.CourseRankItem, error) {
	type Result struct {
		CourseID          uint
		CourseName        string
		CoachName         string
		TotalBookings     int64
		CompletedBookings int64
	}

	var results []Result

	query := r.db.Table("course_bookings cb").
		Select(`cb.course_id, c.name as course_name, COALESCE(co.name, '') as coach_name,
				COUNT(*) as total_bookings,
				SUM(CASE WHEN cb.status = 'COMPLETED' THEN 1 ELSE 0 END) as completed_bookings`).
		Joins("LEFT JOIN courses c ON c.id = cb.course_id").
		Joins("LEFT JOIN coaches co ON co.id = c.coach_id").
		Where("cb.created_at BETWEEN ? AND ?", startDate, endDate).
		Group("cb.course_id, c.name, co.name")

	// 排序
	switch orderBy {
	case "attendance":
		query = query.Order("completed_bookings DESC")
	default:
		query = query.Order("total_bookings DESC")
	}

	if limit > 0 {
		query = query.Limit(limit)
	}

	err := query.Scan(&results).Error
	if err != nil {
		return nil, err
	}

	// 转换结果并计算百分比
	var rankings []model.CourseRankItem
	for i, r := range results {
		attendanceRate := 0.0
		if r.TotalBookings > 0 {
			attendanceRate = float64(r.CompletedBookings) / float64(r.TotalBookings) * 100
		}

		rankings = append(rankings, model.CourseRankItem{
			Rank:              i + 1,
			CourseID:          r.CourseID,
			CourseName:        r.CourseName,
			CoachName:         r.CoachName,
			TotalBookings:     r.TotalBookings,
			CompletedBookings: r.CompletedBookings,
			AttendanceRate:    attendanceRate,
			AvgCapacityRate:   0, // TODO: 需要课程容量数据
		})
	}

	return rankings, nil
}

func (r *statisticsRepository) GetCourseBookingTrends(courseID *uint, startDate, endDate time.Time, groupBy string) (*model.CourseBookingTrendsResp, error) {
	type DailyStats struct {
		Date      string
		Bookings  int64
		Completed int64
	}

	// 根据groupBy参数设置日期格式
	var dateFormat string
	switch groupBy {
	case "week":
		dateFormat = "%Y-%u"
	case "month":
		dateFormat = "%Y-%m"
	default:
		dateFormat = "%Y-%m-%d"
	}

	// 使用安全的参数化查询
	// 注意：GORM的Group方法不支持参数化，所以我们需要确保dateFormat是安全的
	// 由于dateFormat是内部确定的值，不是用户输入，所以是安全的
	query := r.db.Model(&model.CourseBooking{}).
		Select("DATE_FORMAT(created_at, ?) as date, COUNT(*) as bookings, SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) as completed", dateFormat).
		Where("created_at BETWEEN ? AND ?", startDate, endDate).
		Group("date").
		Order("date")

	if courseID != nil {
		query = query.Where("course_id = ?", *courseID)
	}

	var dailyStats []DailyStats
	err := query.Scan(&dailyStats).Error
	if err != nil {
		return nil, err
	}

	// 统计总数
	var totalBookings, totalCompleted, totalCancelled, totalNoShow int64
	var trends []model.BookingTrendData

	for _, stat := range dailyStats {
		totalBookings += stat.Bookings
		totalCompleted += stat.Completed

		attendanceRate := 0.0
		if stat.Bookings > 0 {
			attendanceRate = float64(stat.Completed) / float64(stat.Bookings) * 100
		}

		trends = append(trends, model.BookingTrendData{
			Date:           stat.Date,
			Bookings:       stat.Bookings,
			Completed:      stat.Completed,
			AttendanceRate: attendanceRate,
		})
	}

	resp := &model.CourseBookingTrendsResp{
		Period:         startDate.Format("2006-01-02") + " ~ " + endDate.Format("2006-01-02"),
		TotalBookings:  totalBookings,
		TotalCompleted: totalCompleted,
		TotalCancelled: totalCancelled,
		TotalNoShow:    totalNoShow,
		BookingTrends:  trends,
	}

	return resp, nil
}

func (r *statisticsRepository) GetTimeslotStats(startDate, endDate time.Time) ([]model.TimeslotData, error) {
	type Result struct {
		Hour     int
		Bookings int64
	}

	var results []Result
	err := r.db.Table("course_bookings cb").
		Select("HOUR(cb.created_at) as hour, COUNT(*) as bookings").
		Joins("JOIN courses c ON c.id = cb.course_id").
		Where("cb.created_at BETWEEN ? AND ?", startDate, endDate).
		Group("HOUR(cb.created_at)").
		Order("hour").
		Scan(&results).Error

	if err != nil {
		return nil, err
	}

	var timeslotStats []model.TimeslotData
	for _, r := range results {
		timeslotStats = append(timeslotStats, model.TimeslotData{
			Hour:            r.Hour,
			Label:           formatTimeLabel(r.Hour),
			Bookings:        r.Bookings,
			AvgCapacityRate: 0, // TODO: 需要计算容量利用率
		})
	}

	return timeslotStats, nil
}

// ==================== 教练统计实现 ====================

func (r *statisticsRepository) GetTotalCoaches() (int64, error) {
	var count int64
	err := r.db.Model(&model.Coach{}).Count(&count).Error
	return count, err
}

func (r *statisticsRepository) GetActiveCoachesCount() (int64, error) {
	var count int64
	// 假设有 status 字段，如果没有则返回总数
	err := r.db.Model(&model.Coach{}).Count(&count).Error
	return count, err
}

func (r *statisticsRepository) GetCoachWorkload(coachID *uint, startDate, endDate time.Time) ([]model.CoachWorkload, error) {
	type Result struct {
		CoachID           uint
		CoachName         string
		TotalCourses      int64
		TotalBookings     int64
		CompletedBookings int64
	}

	var results []Result
	query := r.db.Table("courses c").
		Select(`c.coach_id, co.name as coach_name,
				COUNT(DISTINCT c.id) as total_courses,
				COUNT(cb.id) as total_bookings,
				SUM(CASE WHEN cb.status = 'COMPLETED' THEN 1 ELSE 0 END) as completed_bookings`).
		Joins("LEFT JOIN coaches co ON co.id = c.coach_id").
		Joins("LEFT JOIN course_bookings cb ON cb.course_id = c.id AND cb.created_at BETWEEN ? AND ?", startDate, endDate).
		Group("c.coach_id, co.name")

	if coachID != nil {
		query = query.Where("c.coach_id = ?", *coachID)
	}

	err := query.Scan(&results).Error
	if err != nil {
		return nil, err
	}

	var workloads []model.CoachWorkload
	for _, r := range results {
		attendanceRate := 0.0
		if r.TotalBookings > 0 {
			attendanceRate = float64(r.CompletedBookings) / float64(r.TotalBookings) * 100
		}

		workloads = append(workloads, model.CoachWorkload{
			CoachID:           r.CoachID,
			CoachName:         r.CoachName,
			Specialties:       []string{}, // TODO: 需要从教练表获取
			TotalCourses:      r.TotalCourses,
			TotalBookings:     r.TotalBookings,
			CompletedBookings: r.CompletedBookings,
			AttendanceRate:    attendanceRate,
			TotalHours:        0, // TODO: 需要计算
			AvgRating:         0, // TODO: 需要评分数据
			RatingCount:       0,
		})
	}

	return workloads, nil
}

func (r *statisticsRepository) GetCoachRanking(startDate, endDate time.Time, orderBy string, limit int) ([]model.CoachRankItem, error) {
	type Result struct {
		CoachID       uint
		CoachName     string
		TotalBookings int64
	}

	var results []Result
	query := r.db.Table("course_bookings cb").
		Select(`c.coach_id, co.name as coach_name, COUNT(*) as total_bookings`).
		Joins("JOIN courses c ON c.id = cb.course_id").
		Joins("LEFT JOIN coaches co ON co.id = c.coach_id").
		Where("cb.created_at BETWEEN ? AND ?", startDate, endDate).
		Group("c.coach_id, co.name").
		Order("total_bookings DESC")

	if limit > 0 {
		query = query.Limit(limit)
	}

	err := query.Scan(&results).Error
	if err != nil {
		return nil, err
	}

	var rankings []model.CoachRankItem
	for i, r := range results {
		rankings = append(rankings, model.CoachRankItem{
			Rank:          i + 1,
			CoachID:       r.CoachID,
			CoachName:     r.CoachName,
			TotalBookings: r.TotalBookings,
			AvgRating:     0, // TODO: 需要评分数据
			TotalHours:    0, // TODO: 需要计算
		})
	}

	return rankings, nil
}

// ==================== 营收统计实现 ====================

func (r *statisticsRepository) GetTotalRevenue() (float64, error) {
	var total float64
	err := r.db.Model(&model.RechargeRecord{}).
		Where("payment_status = ?", "SUCCESS").
		Select("COALESCE(SUM(actual_amount), 0)").
		Scan(&total).Error
	return total, err
}

func (r *statisticsRepository) GetRevenueByDateRange(startDate, endDate time.Time) (float64, error) {
	var total float64
	err := r.db.Model(&model.RechargeRecord{}).
		Where("payment_status = ? AND paid_at BETWEEN ? AND ?", "SUCCESS", startDate, endDate).
		Select("COALESCE(SUM(actual_amount), 0)").
		Scan(&total).Error
	return total, err
}

func (r *statisticsRepository) GetRevenueByPaymentMethod(startDate, endDate time.Time) ([]model.PaymentMethodStats, error) {
	type Result struct {
		Method string
		Amount float64
		Count  int64
	}

	var results []Result
	err := r.db.Model(&model.RechargeRecord{}).
		Select("payment_method as method, SUM(actual_amount) as amount, COUNT(*) as count").
		Where("payment_status = ? AND paid_at BETWEEN ? AND ?", "SUCCESS", startDate, endDate).
		Group("payment_method").
		Scan(&results).Error

	if err != nil {
		return nil, err
	}

	// 计算总额和百分比
	var totalAmount float64
	for _, r := range results {
		totalAmount += r.Amount
	}

	var stats []model.PaymentMethodStats
	for _, r := range results {
		percentage := 0.0
		if totalAmount > 0 {
			percentage = r.Amount / totalAmount * 100
		}
		stats = append(stats, model.PaymentMethodStats{
			Method:     r.Method,
			Amount:     r.Amount,
			Percentage: percentage,
			Count:      r.Count,
		})
	}

	return stats, nil
}

func (r *statisticsRepository) GetRevenueTrends(startDate, endDate time.Time, groupBy, revenueType string) ([]model.RevenueTrendData, error) {
	var dateFormat string
	switch groupBy {
	case "week":
		dateFormat = "%Y-%u"
	case "month":
		dateFormat = "%Y-%m"
	default:
		dateFormat = "%Y-%m-%d"
	}

	type Result struct {
		Date         string
		TotalRevenue float64
		OrderCount   int64
	}

	var results []Result
	err := r.db.Model(&model.RechargeRecord{}).
		Select(`DATE_FORMAT(paid_at, ?) as date,
				SUM(actual_amount) as total_revenue,
				COUNT(*) as order_count`, dateFormat).
		Where("payment_status = ? AND paid_at BETWEEN ? AND ?", "SUCCESS", startDate, endDate).
		Group("date").
		Order("date").
		Scan(&results).Error

	if err != nil {
		return nil, err
	}

	var trends []model.RevenueTrendData
	for _, r := range results {
		trends = append(trends, model.RevenueTrendData{
			Date:           r.Date,
			TotalRevenue:   r.TotalRevenue,
			RechargeAmount: r.TotalRevenue, // 简化处理
			CardAmount:     0,
			OrderCount:     r.OrderCount,
		})
	}

	return trends, nil
}

func (r *statisticsRepository) GetConsumptionStats(startDate, endDate time.Time, consumeType *string) (*model.ConsumptionStatsResp, error) {
	type TypeStats struct {
		Type   string
		Amount float64
		Count  int64
	}

	query := r.db.Model(&model.ConsumptionRecord{}).
		Select("consume_type as type, SUM(amount) as amount, COUNT(*) as count").
		Where("created_at BETWEEN ? AND ?", startDate, endDate).
		Group("consume_type")

	if consumeType != nil {
		query = query.Where("consume_type = ?", *consumeType)
	}

	var typeStats []TypeStats
	err := query.Scan(&typeStats).Error
	if err != nil {
		return nil, err
	}

	// 计算总计
	var totalAmount float64
	var totalCount int64
	for _, ts := range typeStats {
		totalAmount += ts.Amount
		totalCount += ts.Count
	}

	avgPerOrder := 0.0
	if totalCount > 0 {
		avgPerOrder = totalAmount / float64(totalCount)
	}

	// 转换为响应格式
	var byType []model.ConsumptionByType
	for _, ts := range typeStats {
		percentage := 0.0
		if totalAmount > 0 {
			percentage = ts.Amount / totalAmount * 100
		}
		byType = append(byType, model.ConsumptionByType{
			Type:       ts.Type,
			Amount:     ts.Amount,
			Count:      ts.Count,
			Percentage: percentage,
		})
	}

	return &model.ConsumptionStatsResp{
		Period:           startDate.Format("2006-01-02") + " ~ " + endDate.Format("2006-01-02"),
		TotalConsumption: totalAmount,
		TotalCount:       totalCount,
		AvgPerOrder:      avgPerOrder,
		ByType:           byType,
	}, nil
}

// ==================== 会员卡统计实现 ====================

func (r *statisticsRepository) GetActiveCardsCount() (int64, error) {
	var count int64
	err := r.db.Model(&model.MembershipCard{}).
		Where("status = ?", "ACTIVE").
		Count(&count).Error
	return count, err
}

func (r *statisticsRepository) GetTotalCardsBalance() (float64, error) {
	var total float64
	err := r.db.Model(&model.MembershipCard{}).
		Where("status = ?", "ACTIVE").
		Select("COALESCE(SUM(balance), 0)").
		Scan(&total).Error
	return total, err
}

func (r *statisticsRepository) GetExpiringSoonCardsCount(days int) (int64, error) {
	var count int64
	expireDate := time.Now().AddDate(0, 0, days)
	err := r.db.Model(&model.MembershipCard{}).
		Where("status = ? AND expire_date <= ? AND expire_date > ?", "ACTIVE", expireDate, time.Now()).
		Count(&count).Error
	return count, err
}

// ==================== 辅助函数 ====================

func formatTimeLabel(hour int) string {
	return fmt.Sprintf("%02d:00-%02d:00", hour, hour+1)
}
